package com.h3c.ptability.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.h3c.ptability.cache.UserCache;
import com.h3c.ptability.entity.PtFileTemplate;
import com.h3c.ptability.mapper.PtFileTemplateMapper;
import com.h3c.ptability.utils.AppBusinessException;
import org.apache.commons.text.StringEscapeUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
public class FileOperateService {

    @Autowired
    IPtFileTemplateService ptFileTemplateService;

    @Autowired
    PtFileTemplateMapper ptFileTemplateMapper;

    @Autowired
    CommService commService;

    @Autowired
    IPtUploadFileService ptUploadFileService;

    public String parseUploadFile(String templateCode, MultipartFile uploadFile) {
        if (uploadFile == null || uploadFile.getOriginalFilename().equals("")) {
            throw new RuntimeException("上传文件为空");
        }
        PtFileTemplate ptFileTemplateDO = ptFileTemplateService.queryTemplateByCode(templateCode);

        String fileName = uploadFile.getOriginalFilename();
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (fileType.toLowerCase().equals(".xlsx")) {
            return parseExcelWithXSSFWorkbook(uploadFile, ptFileTemplateDO, fileType, ptFileTemplateDO.getFileTemplateId());
        } else {
            return parseExcelWithHSSFWorkbook(uploadFile, ptFileTemplateDO, fileType, ptFileTemplateDO.getFileTemplateId());
        }
    }

    public String parseUploadFile(String templateCode, MultipartFile uploadFile,String appId) {
        if (uploadFile == null || uploadFile.getOriginalFilename().equals("")) {
            throw new RuntimeException("上传文件为空");
        }
        //文件日志
        ptUploadFileService.saveUploadHistory(uploadFile,appId,"-1", UserCache.getLoginUserId(),commService.getLoginUserNameWithNoException(),commService.getIp(),uploadFile.getOriginalFilename());
//        PtFileTemplate ptFileTemplateDO = ptFileTemplateService.queryTemplateByCode(templateCode);
        PtFileTemplate ptFileTemplateDO = ptFileTemplateMapper.selectOne(new QueryWrapper<PtFileTemplate>()
                .eq("template_code",templateCode)
                .eq("app_id",appId)) ;
        String fileName = uploadFile.getOriginalFilename();
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (fileType.toLowerCase().equals(".xlsx")) {
            return parseExcelWithXSSFWorkbook(uploadFile, ptFileTemplateDO, fileType, ptFileTemplateDO.getFileTemplateId());
        } else {
            return parseExcelWithHSSFWorkbook(uploadFile, ptFileTemplateDO, fileType, ptFileTemplateDO.getFileTemplateId());
        }
    }

    /**
     * excel2003的xls格式文件处理
     *
     * @param uploadFile
     * @param pdFileTemplateDO
     * @param templateId
     * @return
     */
    public String parseExcelWithHSSFWorkbook(MultipartFile uploadFile, PtFileTemplate pdFileTemplateDO, String fileType, String templateId) {
        int startRow = pdFileTemplateDO.getStartRow() - 1;
        int startCol = pdFileTemplateDO.getStartCol() - 1;
        int colSize = pdFileTemplateDO.getColSize();
        int endCol = startCol + colSize;
        String fieldContent = pdFileTemplateDO.getFieldContent();
        String[] fieldArray = fieldContent.split(";");

        StringBuilder sb = new StringBuilder();

        InputStream inputStream = null;
        try {
            //1、获取文件输入流
            inputStream = uploadFile.getInputStream();
            //2、获取Excel工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
            //3、得到Excel工作表对象
            HSSFSheet sheetAt = workbook.getSheetAt(0);
            int lastRowNum = sheetAt.getLastRowNum();
            sb.append("[\n");
            int curRowNum = 0;
            //4、循环读取表格数据
            for (Row row : sheetAt) {
                //首行（即表头）不读取
                if (curRowNum++ < startRow) {
                    continue;
                }
                if (StringUtils.isEmpty(row.getCell(0).getStringCellValue())) {
                    continue;
                }
                sb.append("  { \n");
                int fieldIndex = 0;
                for (int i = startCol; i < endCol; i++) {
                    String fieldName = fieldArray[fieldIndex++];
                    if (fieldName.trim().equals("")) {
                        continue;
                    }
                    //获取单元格
                    Cell cell = row.getCell(i);
                    if (cell != null) {
                        //设置单元格类型
                        cell.setCellType(CellType.STRING);
                        String stringCellValue = cell.getStringCellValue();
                        sb.append("    \"").append(fieldName).append("\":\"").append(stringCellValue).append("\"");
                    } else {
                        sb.append("    \"").append(fieldName).append("\":\"\"");
                    }
                    if (i < endCol) {
                        sb.append(",");
                    }
                    sb.append("\n");
                }
                String curValue = sb.toString();
                if (curValue.substring(curValue.length() - 2).equals(",\n")) {
                    System.out.println(curValue.substring(0, curValue.length() - 2));
                }
                sb.append("  }");
                if (curRowNum <= lastRowNum) {
                    sb.append(", ");
                }
                sb.append("\n");
            }
            sb.append("]\n");
            //5、关闭流
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("导入模板,文件解析时出错");
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                    throw new RuntimeException("导入模板,保存模板文件时解析出错");
                }
                inputStream = null;
            }
        }
        String contentValue = sb.toString();
        if(!StringUtils.isEmpty(contentValue) && contentValue.indexOf("&amp;")>0){
            contentValue = contentValue.replaceAll("&amp;","&");
        }
        return contentValue;
    }

    public static void main(String[] args) {
        String curValue = "a:1,\n";
        if (curValue.substring(curValue.length() - 2).equals(",\n")) {
            System.out.println(curValue.substring(0, curValue.length() - 2));
        }
    }

    /**
     * EXCEL 2007以后的xlsx格式文件处理
     *
     * @param uploadFile
     * @param pdFileTemplateDO
     * @param templateId
     * @return
     */
    public String parseExcelWithXSSFWorkbook(MultipartFile uploadFile, PtFileTemplate pdFileTemplateDO, String fileType, String templateId) {
        int startRow = pdFileTemplateDO.getStartRow() - 1;
        int startCol = pdFileTemplateDO.getStartCol() - 1;
        int colSize = pdFileTemplateDO.getColSize();
        int endCol = startCol + colSize;
        String fieldContent = pdFileTemplateDO.getFieldContent();
        String[] fieldArray = fieldContent.split(";");

        StringBuilder sb = new StringBuilder();

        InputStream inputStream = null;
        try {
            //1、获取文件输入流
            inputStream = uploadFile.getInputStream();
            //2、获取Excel工作簿对象
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
//            HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook); //计算公式中的值；
            //3、得到Excel工作表对象
            XSSFSheet sheetAt = workbook.getSheetAt(0);
            int lastRowNum = sheetAt.getLastRowNum();
            sb.append("[\n");
            int curRowNum = 0;
            //4、循环读取表格数据
            for (Row row : sheetAt) {
                //首行（即表头）不读取
                if (curRowNum++ < startRow) {
                    continue;
                }
                sb.append("  { \n");
                int fieldIndex = 0;
                for (int i = startCol; i < endCol; i++) {
                    String fieldName = fieldArray[fieldIndex++];
                    if (fieldName.trim().equals("")) {
                        continue;
                    }
                    //获取单元格
                    Cell cell = row.getCell(i);
                    if (cell != null) {

                        if (fieldName.toLowerCase().indexOf("date") < 0) {
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                        }


                        //sb.append("    \"").append(fieldName).append("\":\"").append(row.getCell(i).getStringCellValue()).append("\"");
                        //String stringCellValue = getCellValue(cell).trim();
                        String stringCellValue = getCellValue(cell) == null ? "" : getCellValue(cell).trim();
                        if (fieldName.equals("workerId") && !StringUtils.isEmpty(stringCellValue)) {
                            StringBuilder workerId = new StringBuilder();
                            for (int l = 0; l < 5 - stringCellValue.length(); l++) {
                                workerId.append("0");
                            }
                            stringCellValue = workerId.append(stringCellValue).toString();
                        }
                        if (!pdFileTemplateDO.getAppId().equals("hr-leaderexam") && (fieldName.indexOf("kpis") >= 0 || fieldName.indexOf("cultures") >= 0 || fieldName.indexOf("lastYearKpi") >= 0 || fieldName.indexOf("lastYearCulture") >= 0)) { //处理绩效文化数据
                            String seasonName = getCellValue(sheetAt.getRow(0).getCell(i));
                            if (seasonName.indexOf("X") >= 0) {
                                throw new AppBusinessException("请检查绩效及文化行为评价的表头设置是否正确，可参考格式：2022年度绩效，2022年Q1绩效");
                            }
                            stringCellValue = replaceChinese(seasonName, "") + ":" + stringCellValue;
                        }
                        sb.append("    \"").append(fieldName).append("\":\"").append(StringEscapeUtils.escapeJson(StringEscapeUtils.escapeHtml4(stringCellValue))).append("\"");

                    } else {
                        sb.append("    \"").append(fieldName).append("\":\"\"");
                    }
                    if (i < endCol) {
                        sb.append(",");
                    }
                    sb.append("\n");
                }
                String curValue = sb.toString();
                if (curValue.substring(curValue.length() - 2).equals(",\n")) {
                    sb = new StringBuilder(curValue.substring(0, curValue.length() - 2));
                }
                sb.append("\n  }");
                if (curRowNum <= lastRowNum) {
                    sb.append(", ");
                }
                sb.append("\n");
            }
            sb.append("]\n");
            //5、关闭流
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("导入模板,文件解析时出错");
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                    throw new RuntimeException("导入模板,保存模板文件时解析出错");
                }
                inputStream = null;
            }
        }
        String contentValue = sb.toString();
        if(!StringUtils.isEmpty(contentValue) && contentValue.indexOf("&amp;")>0){
            contentValue = contentValue.replaceAll("&amp;","&");
        }
        return contentValue;
    }

    private String getCellValue(Cell cell){
        String val;
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
        DecimalFormat    df = new DecimalFormat("0");
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    val = sf.format(cell.getDateCellValue()); //日期型
                } else {
                    val = df.format(cell.getNumericCellValue()); //数字型
                }
                break;
            case XSSFCell.CELL_TYPE_STRING: //文本类型
                val = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN: //布尔型
                val = String.valueOf(cell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK: //空白
                val = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_ERROR: //错误
                val = "错误";
                break;
            case XSSFCell.CELL_TYPE_FORMULA: //公式
                try {
                    val = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    val = String.valueOf(cell.getNumericCellValue());
                }
                break;
            default:
                val = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString();
        }
        return val;
    }


    public String replaceChinese(String source, String replacement) {
        if (StringUtils.isEmpty(source)) {
            return null;
        }
        if (replacement == null) {
            replacement = "";
        }
        String reg = "[\u4e00-\u9fa5]";
        Pattern pat = Pattern.compile(reg);
        Matcher mat = pat.matcher(source);
        String repickStr = mat.replaceAll(replacement);
        return repickStr;
    }

}
